package com.google.code.gestion_de_conferences;


import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class AABDatabaseManager
{
	// the Activity or Application that is creating an object from this class.
	Context context;

	// a reference to the database used by this application/object
	public SQLiteDatabase db;

	// These constants are specific to the database.  They should be
	// changed to suit your needs.
	private final String DB_NAME = "Gestion_v1";
	private final int DB_VERSION = 1;

	//User's info
	private String[] user_fields = new String[]{"_id", "name", "email", "company", "phone", "pass","is_admin"};
	private final String TABLE_USER = "user";
	//Room's info
	private String[] room_fields = new String[]{"_id", "name", "address", "capacity", "description"};
	private final String TABLE_ROOM = "room";
	//Reservation's info
	private String[] reservation_fields = new String[]{"_id", "date", "from_date", "to_date", "room", "user"};
	private final String TABLE_RESERVATION = "reservation";

	public AABDatabaseManager(Context context)
	{
		this.context = context;

		// create or open the database
		CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
		this.db = helper.getWritableDatabase();
	}

	/**
	 * This function inserts rows: It knws to which table to save according to the type of the class sent
	 * @author Rayan Zahab <differentruna@gmail.com>
	 * @param name
	 * @param username
	 * @param pass
	 * @param pass2
	 * @param phone
	 * @param is_admin 
	 */
	public void insertRow(Object o)
	{

		String[] fields = new String[]{};
		String[] values = new String[]{};
		String tableName = "";
		String cl_name = o.getClass().getSimpleName();
		if(cl_name.equals("User"))
		{
			fields = user_fields;
			tableName = TABLE_USER;
			User u= (User) o;
			values = new String[]{u.getFullName(), u.getEmail(),u.getCompany(), u.getPhoneNumber(), u.getPassword(), u.getIsAdmin()};
		}else if(cl_name.equals("Room"))
		{
			fields = room_fields;
			tableName = TABLE_ROOM;
			Room r= (Room) o;
			values = new String[]{r.getName(),r.getAddress(),r.getCapacity(),r.getDescription()};
		}else if(cl_name.equals("Reservation"))
		{
			fields = reservation_fields;
			tableName = TABLE_RESERVATION;
			Reservation res= (Reservation) o;
			Date date=res.getDate();
			String date_str =date.toString();
			String roomId = ""+res.getRoomId();
			String userId = ""+res.getUserId(); 
			values = new String[]{date_str,res.getFrom(),res.getTo(),roomId,userId};
		}

		ContentValues content = new ContentValues();
		for (int position=1; position < fields.length; position++)
    	{
			String field = fields[position];
			String value = values[position-1];
			content.put(field,value);
    	}
		try{
			db.insert(tableName, null, content);
		}
		catch(Exception e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}

	/**
	 * UPDATING A ROW IN THE DATABASE TABLE
	 * @author Rayan Zahab
	 * @param Object to delete
	 */
	public void deleteRow(Object o)
	{
		String tableName = "";
		int id=0;
		String cl_name = o.getClass().getSimpleName();
		if(cl_name.equals("User"))
		{
			tableName = "user";
			User u= (User) o;
			id = u.getID();
			try {
				db.delete(TABLE_RESERVATION, " user =" + id, null);
				}
			catch (Exception e)
			{
				Log.e("DB ERROR", e.toString());
				e.printStackTrace();
			}
		}else if(cl_name.equals("Room"))
		{
			tableName = TABLE_ROOM;
			Room r= (Room) o;
			id = r.getId();
			try {
				db.delete(TABLE_RESERVATION, " room =" + id, null);
				}
			catch (Exception e)
			{
				Log.e("DB ERROR", e.toString());
				e.printStackTrace();
			}
		}else if(cl_name.equals("Reservation"))
		{
			tableName = TABLE_RESERVATION;
			Reservation res= (Reservation) o;
			id = res.getId();
		}
		// ask the database manager to delete the row of given id
		try {db.delete(tableName, " _id =" + id, null);}
		catch (Exception e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}

	/**
	 * UPDATING A ROW IN THE DATABASE TABLE
	 * @author Rayan Zahab
	 * @param Object to edit having the old id, and new values
	 */
	public void updateRow(Object o)
	{
		String[] fields = new String[]{};
		String[] values = new String[]{};
		String tableName = "";
		int id=0;
		String cl_name = o.getClass().getSimpleName();
		if(cl_name.equals("User"))
		{
			fields = user_fields;
			tableName = "user";
			User u= (User) o;
			id = u.getID();
			values = new String[]{u.getFullName(), u.getEmail(),u.getCompany(), u.getPhoneNumber(), u.getPassword(), u.getIsAdmin()};
		}else if(cl_name.equals("Room"))
		{
			fields = room_fields;
			tableName = "room";
			Room r = (Room) o;
			id = r.getId();
			values = new String[]{r.getName(),r.getAddress(),r.getCapacity(),r.getDescription()};
		}else if(cl_name.equals("Reservation"))
		{
			fields = reservation_fields;
			tableName = "reservation";
			Reservation res = (Reservation) o;
			Date date = res.getDate();
			String date_str = date.toString();
			values = new String[]{date_str,res.getFrom(),res.getTo(),""+res.getRoomId(),""+res.getUserId()};
		}

		ContentValues content = new ContentValues();
		for (int position=1; position < fields.length; position++)
    	{
			String field = fields[position];
			String value = values[position-1];
			content.put(field,value);
    	}
		// ask the database object to update the database row of given rowID
		try {db.update(tableName, content, "_id" + "=" + id, null);}
		catch (Exception e)
		{
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
	}

	/**
	 * @author Rayan Zahab
	 * RETRIEVING A ROW FROM A DATABASE TABLE
	 * @param rowID: the id of the row to retrieve, tableName :the table from which to get the row, fields : the fields of the table
	 * @return an array containing the data from the row
	 */
	public ArrayList<Object> getRowAsArray(long rowID,String tableName,String[] fields)
	{
		ArrayList<Object> rowArray = new ArrayList<Object>();
		Cursor cursor;
		try
		{
			cursor = db.query( tableName,fields,	fields[0] + "=" + rowID, null, null, null, null );
			cursor.moveToFirst();
			if (!cursor.isAfterLast())
			{
				do
				{
					for (int position=0; position < fields.length; position++)
			    	{
						rowArray.add(cursor.getString(position));
			    	}
				}
				while (cursor.moveToNext());
			}
			cursor.close();
		}
		catch (SQLException e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
		return rowArray;
	}

	/**
	 *
	 * Check if the login info are right to allow login
	 * @author Rayan Zahab
	 * @param user
	 * @param pass
	 * @param tableName
	 * @param fields
	 * @return
	 */
	public ArrayList<String> getRowAsArrayForLogin(String user,String pass)
	{
		String[] fields = user_fields;
		String tableName = "user";
		ArrayList<String> rowArray = new ArrayList<String>();
		Cursor cursor;
		try
		{
			cursor = db.query( tableName,fields,fields[2] + " LIKE " + "'"+user+"' AND "+fields[5] + " LIKE " + "'"+pass+"' AND "+fields[6] + " LIKE " + "'1'", null, null, null, null );
			cursor.moveToFirst();
			if (!cursor.isAfterLast())
			{
				do
				{
					for (int position=0; position < fields.length; position++)
			    	{
						rowArray.add(cursor.getString(position));
			    	}
				}
				while (cursor.moveToNext());
			}
			cursor.close();
		}
		catch (SQLException e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
		
		return rowArray;
	}
	/**
	 *
	 * Get rows having param=passed value in the paased table
	 * @author Rayan Zahab
	 * @param param: the position of the attribute to companre in the table fields
	 * @param tableName
	 * @param value is the value to search for
	 * @return
	 */
	public ArrayList<ArrayList<Object>> getRowAsArrayByPosition(int param,String tableName,String value)
	{
		String[] fields = user_fields;
		if(tableName.equals("User"))
		{
			fields = user_fields;
		}else if(tableName.equals("Room"))
		{
			fields = room_fields;
		}else if(tableName.equals("Reservation"))
		{
			fields = reservation_fields;
		}
		ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
		Cursor cursor;
		try
		{
			cursor = db.query( tableName,fields,fields[param] + " LIKE '"+ value+"'", null, null, null, null );
			cursor.moveToFirst();
			if (!cursor.isAfterLast())
			{
				do
				{
					ArrayList<Object> dataList = new ArrayList<Object>();
					for (int position=0; position < fields.length; position++)
			    	{
						dataList.add(cursor.getString(position));
			    	}
					dataArrays.add(dataList);
				}
				while (cursor.moveToNext());
			}
			cursor.close();
		}
		catch (SQLException e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
		return dataArrays;
	}
	/**
	 *
	 * Get attribute at position ATT having attribute at position param in tableName
	 * @author Rayan Zahab
	 * @param param: the position of the attribute to companre in the table fields
	 * @param param: the position of the attribute to get it value
	 * @param tableName
	 * @param value is the value to search for
	 * @return
	 */
	public String getValueByParam(int param,int att,String tableName,String value)
	{
		String[] fields = user_fields;
		
		if(tableName.equals("User"))
		{
			fields = user_fields;
		}else if(tableName.equals("Room"))
		{
			fields = room_fields;
		}else if(tableName.equals("Reservation"))
		{
			fields = reservation_fields;
		}
		Cursor cursor;
		String toReturn="";
		try
		{
			cursor = db.query( tableName,fields,fields[param] + " LIKE '"+ value+"'", null, null, null, null );
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast())
			{
				do
				{					
					toReturn = cursor.getString(att);
					cursor.close();
					return toReturn;
				}
				while (cursor.moveToNext());
			}
			
			cursor.close();
		}
		catch (SQLException e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
		return toReturn;
	}
	

	/**
	 * Checks if a username (email) already exists in the db
	 * @author RayanZahab
	 * @param user
	 * @param tableName
	 * @param fields table fields
	 * @return
	 */
	public ArrayList<String> getRowAsArrayByUser(User u)
	{
		ArrayList<String> rowArray = new ArrayList<String>();
		Cursor cursor;
		String[] fields = user_fields;
		String tableName = "user";
		String email = u.getEmail();

		try
		{
			cursor = db.query( tableName,fields,fields[2] + " LIKE " + "'"+email+"'", null, null, null, null );
			cursor.moveToFirst();
			if (!cursor.isAfterLast())
			{
				do
				{
					for (int position=0; position < fields.length; position++)
			    	{
						rowArray.add(cursor.getString(position));
			    	}
				}
				while (cursor.moveToNext());
			}
			cursor.close();
		}
		catch (SQLException e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
		return rowArray;
	}


	/**
	 * RETRIEVING ALL ROWS FROM A DATABASE TABLE
	 * @author Rayan Zahab
	 * @param tableName the table from which to get the row
	 * @param fields  the fields of the table
	 * @return an array containing the data of the table
	 *
	 */
	public ArrayList<ArrayList<Object>> getTablesRowsAsArrays(Object o)
	{
		String tableName = "";
		String[] fields= new String[]{};
		String cl_name = o.getClass().getSimpleName();
		if(cl_name.equals("User"))
		{
			fields = user_fields;
			tableName = "user";
		}else if(cl_name.equals("Room"))
		{
			fields = room_fields;
			tableName = "room";
		}else if(cl_name.equals("Reservation"))
		{
			fields = reservation_fields;
			tableName = "reservation";
		}
		ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
		Cursor cursor;
		try
		{
			cursor = db.query( tableName,fields,	null, null, null, null, null );
			cursor.moveToFirst();
			if (!cursor.isAfterLast())
			{
				do
				{
					ArrayList<Object> dataList = new ArrayList<Object>();
					for (int position=0; position < fields.length; position++)
			    	{
						dataList.add(cursor.getString(position));
			    	}
					dataArrays.add(dataList);
				}while (cursor.moveToNext());
			}
		}
		catch (SQLException e)
		{
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
		return dataArrays;
	}

	/**
	 * This function return the a db object based on its position 
	 * @author Hyame Alameddine <hyame.alameddine@gmail.com>
	 * 
	 * @param position position of the element to get (usually given based on user selection)
	 * @param Object o this will be from type user or room or reservation.
	 *  And will be processed based on the type in the getTablesRowsAsArrays()
	 *  
	 * @return ArrayList<Object>
	 */
	public ArrayList<Object> getObjectFromPosition (int position, Object o)
	{	
		ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();

		//Get all data in the db by passing the object
		dataArrays = this.getTablesRowsAsArrays(o);
		 
		//get the object at the needed position
    	ArrayList<Object> row = dataArrays.get(position);
       
    	return row;
	}
	
	
	/**
	 * @author Rayan Zahab
	 * @param db handler
	 * @param tableName table to create
	 * @param fields fields of the table
	 */
	public void createTable(SQLiteDatabase db,String tableName, String[] fields)
	{
		String query = "create table " +	tableName +	" (" ;
		for (int position=0; position < fields.length; position++)
    	{
			String field = fields[position];
			if(position==0)
			{
				query=query + field + " integer primary key autoincrement not null, ";
			}else if(position!=fields.length-1){
				query=query + field + " text,";
			}else{
				query=query + field + " text" +	");";
			}
    	}
		db.execSQL(query);
	}


	private class CustomSQLiteOpenHelper extends SQLiteOpenHelper
	{
		public CustomSQLiteOpenHelper(Context context)
		{
			super(context, DB_NAME, null, DB_VERSION);
		}
		/**
		 * @author RayanZahab
		 * Creating db tables
		 * @param SQLiteDatabase db
		 */
		@Override
		public void onCreate(SQLiteDatabase db)
		{
			createTable(db,TABLE_USER,user_fields);
			createTable(db,TABLE_ROOM,room_fields);
			createTable(db,TABLE_RESERVATION,reservation_fields);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
		}
	}
}